from generate_common import handleValueApply, handleTriggerApply, handleCustomApply

# ============ than absolute ============
def genThanAbsByDefaultInOneCase(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId):
    # 如果 inOne 是 case，则针对当前一个病例，直接从d表中查出vlt.item_time_seq对应的记录，将超出阈值的数量设为违规数量
    sqlViolate = f"UPDATE VLT SET vlt.vlt_q = (SELECT LEAST(d.accu_q-{value},d.q) FROM d WHERE vlt.item_time_seq = d.item_time_seq AND ROWNUM=1) "
    sqlViolate += f"WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'"
    return sqlViolate


def genThanAbsByDefaultInOneDay(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId):
    # 如果 inOne 是 day，则针对当前患者同一个日期的累计数值
    sqlViolate = f"UPDATE VLT SET vlt.vlt_q = (SELECT LEAST(d.accu_q_day-{value},d.q) FROM d WHERE vlt.item_time_seq = d.item_time_seq AND ROWNUM=1) "
    sqlViolate += f"WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'"
    return sqlViolate

def genThanAbsByDefaultInOneHour(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId):
    # 如果 inOne 是 hour，则针对当前患者同一个小时的累计数值
    sqlViolate = f"UPDATE VLT SET vlt.vlt_q = (SELECT LEAST(d.accu_q_hour-{value},d.q) FROM d WHERE vlt.item_time_seq = d.item_time_seq AND ROWNUM=1) "
    sqlViolate += f"WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'"
    return sqlViolate

def genThanAbsByDefaultInOneYear(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId):

    # 统计包括当前的数据之前，同一年的数据的总和大于阈值的部分，这部分就是违规数量
    sqlViolate = f"""
UPDATE vlt SET vlt.vlt_q = (
    SELECT LEAST(SUM(d.q)-{value},vlt.q)
    FROM d
    WHERE d.psn_no = vlt.psn_no and d.hsp_abbr = vlt.hsp_abbr and d.j_year = vlt.j_year and d.item_time <= vlt.item_time
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanAbsByDefaultInOnePatient(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId):
    # 统计包括当前的数据之前，同一个人的数据的总和大于阈值的部分，这部分就是违规数量
    sqlViolate = f"""
UPDATE vlt SET vlt.vlt_q = (
    SELECT LEAST(SUM(d.q)-{value},vlt.q)
    FROM d
    WHERE d.psn_no = vlt.psn_no and d.hsp_abbr = vlt.hsp_abbr and d.item_time <= vlt.item_time
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanAbsByDefault(operSql, value, inOne, itemCodesSql, itemNamesSql, sql0, hsp_abbr, ruleActId, ruleId):
    sql = sql0
    # 如果 inOne 是 case，则针对当前一个病例 （即同一个 setl_id）进行查询
    if inOne == 'case':
        sqlViolate = genThanAbsByDefaultInOneCase(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 inOne 是 day，则针对当前患者同一个日期的累计数值
    elif inOne == 'day':
        sqlViolate = genThanAbsByDefaultInOneDay(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 inOne 是 hour，则针对当前患者同一个小时的累计数值
    elif inOne == 'hour':
        sqlViolate = genThanAbsByDefaultInOneHour(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 inOne 是 year，则针对当前患者同一个年份的累计数值
    elif inOne == 'year':
        sqlViolate = genThanAbsByDefaultInOneYear(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 inOne 是 patient，则针对当前患者的累计数值
    elif inOne == 'patient':
        sqlViolate = genThanAbsByDefaultInOnePatient(operSql, value, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    return sqlViolate

def genThanAbs(operSql, filterDatum, itemCodesSql, itemNamesSql, sql0, hsp_abbr, ruleActId, ruleId):
    sql = sql0

    by = filterDatum.get('by', 'default')
    value = filterDatum['value']
    inOne = filterDatum.get('in_one_', 'case')

    # 如果 by 是 default，则各个项目直接比对 item_num 字段，任何超出的就是不符合条件的
    if by == 'default':
        sqlViolate = genThanAbsByDefault(operSql, value, inOne, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)

    # 如果 by 是 aggr，即将trigger作为一个总体来判断，总体超就全超，这个时候没有超出部分，所以就将整体数量设为违规数量
    else:
        sqlViolate = "UPDATE vlt SET vlt.vlt_q = vlt.q WHERE vlt.rule_act_id = '{}' AND vlt.rule_id = '{}' AND vlt.hsp_abbr = '{}'".format(ruleActId, ruleId, hsp_abbr)
        
    return sqlViolate
# ============ than absolute END ============


# ============ than period ============
def genThanPeriodByDefault(operSql, plusBeforeTimes, times, plusAfterTimes, itemCodesSql, itemNamesSql, sql0, hsp_abbr, ruleActId, ruleId):
    sqlViolate = f"""
UPDATE VLT SET vlt.vlt_q = (
    SELECT LEAST(
        d.accu_q - ( ({plusBeforeTimes} + d.period) * {times} + {plusAfterTimes}), 
        d.q
    )
    FROM d
    WHERE vlt.item_time_seq = d.item_time_seq AND ROWNUM=1
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanPeriodByAggr(operSql, plusBeforeTimes, times, plusAfterTimes, itemCodesSql, itemNamesSql, sql0, hsp_abbr, ruleActId, ruleId, by):
    # 将 itemCOdesSql 和 itemNamesSql 的 d2. 标签改为 d.标签
    itemCodesSql = itemCodesSql.replace('d2.', 'd.')
    itemNamesSql = itemNamesSql.replace('d2.', 'd.')

    sqlViolate = f"""
UPDATE VLT SET vlt.vlt_q = (
    SELECT LEAST(
        sum(d.q) - ( ({plusBeforeTimes} + max(d.period)) * {times} + {plusAfterTimes}),
        max(vlt.q)
    )
    FROM d
    WHERE ROWNUM=1
        {itemCodesSql} {itemNamesSql}
        AND d.hsp_abbr = '{hsp_abbr}' AND d.psn_no = VLT.psn_no AND d.j_year = VLT.j_year
        AND d.setl_id = VLT.setl_id
        AND d.item_time <= VLT.item_time
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanPeriod(operSql, filterDatum, itemCodesSql, itemNamesSql, sql0, hsp_abbr, ruleActId, ruleId):
    sql = sql0
    # 获得 by
    by = filterDatum.get('by', 'default')

    # 获得 plusBeforeTimes, times, plusAfterTimes 三个参数
    plusBeforeTimes = filterDatum.get('plus_before_times', 0)
    times = filterDatum.get('times', 1)
    plusAfterTimes = filterDatum.get('plus_after_times', 0)

    # 如果 by 是 default，则各个项目直接比对 item_num 字段，任何超出的就是不符合条件的
    if by == 'default':
        sqlViolate = genThanPeriodByDefault(operSql, plusBeforeTimes, times, plusAfterTimes, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 by 是 aggr，则将trigger作为一个总体来判断，总体超就全超，这个时候没有超出部分，所以就将整体数量设为违规数量
    elif by in ('sum', 'min', 'max', 'avg'):
        sqlViolate = genThanPeriodByAggr(operSql, plusBeforeTimes, times, plusAfterTimes, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId, by)

    return sqlViolate
# ============ than period END ============

# ============ than item ============
def genThanItemByDefaultInOneCase(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId):
    sqlViolate = f"""
UPDATE VLT SET vlt.vlt_q = (
    SELECT LEAST( d.accu_q- (
            SELECT SUM(d2.q) FROM d d2 WHERE d.setl_id = d2.setl_id {valueCodesSql} {valueNamesSql}
        ) , d.q
    )
    FROM d
    where vlt.item_time_seq = d.item_time_seq AND ROWNUM=1
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanItemByDefaultInOneDay(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId):
    sqlViolate = f"""
UPDATE VLT SET vlt.vlt_q = (
    SELECT LEAST( d.accu_q- (
            SELECT SUM(d2.q) FROM d d2 WHERE d.setl_id = d2.setl_id AND d.item_day = d2.item_day {valueCodesSql} {valueNamesSql}
        ) , d.q
        
    )
    FROM d
    where vlt.item_time_seq = d.item_time_seq AND ROWNUM=1
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanItemByDefaultInOneHour(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId):
    sqlViolate = f"""
UPDATE VLT SET vlt.vlt_q = (
    SELECT LEAST( d.accu_q- (
            SELECT SUM(d2.q) FROM d d2 WHERE d.setl_id = d2.setl_id AND d.item_day = d2.item_day 
                AND d.item_hour = d2.item_hour {valueCodesSql} {valueNamesSql}
        ) , d.q
        
    )
    FROM d
    where vlt.item_time_seq = d.item_time_seq AND ROWNUM=1
)
    WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'
"""
    return sqlViolate

def genThanItemByDefault(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql0, hsp_abbr, ruleActId, ruleId):
    sql = sql0
    # 获得 inOne
    inOne = filterDatum.get('in_one_', 'case')
    # 如果 inOne 是 case，则针对当前一个病例，than的主动比较对象是item，被动比较对象是value
    if inOne == 'case':
        sqlViolate = genThanItemByDefaultInOneCase(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 inOne 是 day，则针对当前患者同一个日期的累计数值
    elif inOne == 'day':
        sqlViolate = genThanItemByDefaultInOneDay(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 inOne 是 hour，则针对当前患者同一个小时的累计数值
    elif inOne == 'hour':
        sqlViolate = genThanItemByDefaultInOneHour(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId)
    else:
        # 如果 inOne 不是上述两种情况，则直接返回原始 SQL
        # 这可能是一个错误情况，或者是未处理的情况
        print(f"Warning: Unsupported inOne value '{inOne}' for than item.")
    return sqlViolate

def genThanItem(operSql, filterDatum, itemCodesSql, itemNamesSql, sql0, hsp_abbr, ruleActId, ruleId):
    sql = sql0

    # 获得 By, inOne
    by = filterDatum.get('by', 'default')
    inOne = filterDatum.get('in_one_', 'case')

    # 获得value_apply和value，分别制作 valueCodesSql和valueNamesSql
    valueApply = filterDatum.get('value_apply', [])
    value = filterDatum['value']
    valueCodesSql, valueNamesSql = handleCustomApply(valueApply, value, type='item', tblAbbr='d2')
    # 统计valueApply的数量
    valueLen = len(valueApply)

    # 如果 by 是 default，则各个项目直接比对 item_num 字段，任何超出的就是不符合条件的
    if by == 'default':
        sqlViolate = genThanItemByDefault(operSql, filterDatum, itemCodesSql, itemNamesSql, valueCodesSql, valueNamesSql, valueLen, sql, hsp_abbr, ruleActId, ruleId)
    # 如果 by 是 aggr：
    else:
        # 将整体数量设为违规数量
        sqlViolate = f"UPDATE vlt SET vlt.vlt_q = vlt.q WHERE vlt.rule_act_id = '{ruleActId}' AND vlt.rule_id = '{ruleId}' AND vlt.hsp_abbr = '{hsp_abbr}'"
    return sqlViolate
# ============ than item END ============

# ============ 主函数 ============
def genViolateItemNum(ruleDatum, ruleAct, filterDatum, hsp_abbr, sql0):
    sql = sql0

    # 获得 operator，是 gt, lt, eq, gte, lte
    operator = filterDatum['operator']
    operSql = ""
    if operator == 'gt':
        operSql = ">"
    elif operator == 'lt':
        operSql = "<"
    elif operator == 'eq':
        operSql = "="
    elif operator == 'gte':
        operSql = ">="
    elif operator == 'lte':
        operSql = "<="

    than = filterDatum.get('than', 'absolute')

    # 获得 of，默认是 ['trigger']
    of = filterDatum.get('of', ['trigger'])
    itemObjApply = []
    itemNames = []
    if 'trigger' in of:
        # 从ruleAct中获取 trigger 与 trigger_apply
        itemObjApply += ruleAct.get('trigger_apply', [])
        itemNames += filterDatum.get('trigger', [])
    if 'item' in of:
        # 遍历ruleAct['filter']，如果算子的field是'item'，则将value加上去
        for filterDatumItem in ruleAct.get('filter', []):
            if filterDatumItem.get('field') == 'item':
                itemObjApply += filterDatumItem.get('value_apply', [])
                itemNames += filterDatumItem.get('value', [])
    if 'itemextra' in of:
        # 从ruleAct中获取 itemextra 与 itemextra_apply
        itemObjApply += ruleAct.get('itemextra_apply', [])
        itemNames += filterDatum.get('itemextra', [])
    # 对itemObjApply、itemNames制作sql语句
    itemCodesSql, itemNamesSql = handleCustomApply(itemObjApply, itemNames, type='item', tblAbbr='d2')

    # 获得 ruleActId 和 ruleId
    ruleActId = ruleAct['ruleActId']
    ruleId = ruleDatum['ruleId']

    # 构建SQL
    # 如果 than 是 absolute，则直接使用 value
    if than == 'absolute':
        sql += f"-- condition about item_num comparing to an absolute\n"
        sql = genThanAbs(operSql, filterDatum, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)
    
    # 如果 than 是 period，则说明比较的对象是依据当次住院天数变化的相对值
    elif than == 'period':
        sql += f"-- condition about item_num period comparing to period\n"
        sql = genThanPeriod(operSql, filterDatum, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)

    # 如果 than 是 item，则说明比较的对象是具体的项目的数量
    elif than == 'item':
        sql += f"-- condition about item_num comparing to item\n"
        sql = genThanItem(operSql, filterDatum, itemCodesSql, itemNamesSql, sql, hsp_abbr, ruleActId, ruleId)

    return sql